package com.pointwest.pls.constant;

public class DBConstant {

	public static final String SQL_EXCEPTION = "sqlException occured";
	public static final String SQL_PASSWORD = "password";
	public static final String SQL_USERNAME = "root";
	public static final String URL = "jdbc:mysql://localhost:3306/pls";
	public static final String DRIVER = "com.mysql.jdbc.Driver";

	// sql statements
	public static final String SQL_SEARCH_EMPLOYEE_FULL_BY_ID = "SELECT employee.first_name, employee.last_name, project.project_desc, seat.seat_code, seat_type.seat_type_desc, seat.quadrant, seat.local_no FROM employee INNER JOIN seat ON (employee.employee_id) = seat.employee_id INNER JOIN seat_type ON (seat.seat_id = seat_type.seat_type_id) INNER JOIN resource ON (employee.employee_id = resource.employee_id) INNER JOIN project ON (resource.project_id = project.project_id) WHERE employee.employee_id = ?";
	public static final String SQL_SEARCH_SEAT_BY_ID = "SELECT employee.first_name, employee.last_name, seat.seat_code, seat_type.seat_type_desc, seat.quadrant, seat.local_no FROM employee INNER JOIN seat ON (employee.employee_id = seat.employee_id) INNER JOIN seat_type ON (seat.seat_type_id = seat_type.seat_type_id) WHERE employee.employee_id = ?";
	public static final String SQL_SEARCH_PROJECT_BY_ID = "SELECT project.project_desc FROM resource INNER JOIN project ON (resource.project_id = project.project_id) WHERE resource.employee_id = ?";
	public static final String SQL_GET_MAP_BY_QUADRANT_AND_LOC_ID= "SELECT seat_code, employee_id, local_no, seat_type.seat_type_desc, row, col FROM seat INNER JOIN seat_type ON (seat.seat_type_id = seat_type.seat_type_id) WHERE quadrant = ? AND location_detail_id = ? ORDER BY row, col;";
	public static final String SQL_UPDATE_SEAT = "UPDATE seat SET employee_id = ? WHERE seat_code = ?";
	public static final String SQL_CHECK_SEAT_AVAILABILITY = "SELECT employee_id FROM seat WHERE seat_code = ? AND employee_id IS NOT NULL";
	public static final String SQL_INSERT_EMPLOYEE_TO_SEAT = "INSERT INTO seat (employee_id, seat_code, quadrant, location_detail_id, seat_type_id, col, row) VALUES (?, ?, ?, ?, ?, ?, ?)";
	public static final String SQL_DELETE_EMPLOYEE = "DELETE FROM seat WHERE employee_id = ? AND seat_code = ?";
	
	//columns for mapping
	public static final String COL_EMPLOYEE_ID = "employee_id";
	public static final String COL_FIRST_NAME = "first_name";
	public static final String COL_LAST_NAME = "last_name";
	public static final String COL_EMAIL_ADD = "email_add";
	public static final String COL_POSITION_CODE = "position_code";
	public static final String COL_PROJECT_DESC = "project_desc";
	public static final String COL_SEAT_CODE = "seat_code";
	public static final String COL_SEAT_TYPE_DESC = "seat_type_desc";
	public static final String COL_QUADRANT = "quadrant";
	public static final String COL_LOCAL_NO = "local_no";
	public static final String COL_COLUMN = "col";
	public static final String COL_ROW = "row";
}
